package com.bw.mapper;


import com.bw.bean.Vo;
import com.bw.bean.Vo1;
import com.bw.bean.Vo2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface VoMapper {

    @Select("select bp.name,sum(oi.final_total_amount) `value`from order_info oi\n" +
            "left join base_province bp on bp.id = oi.province_id\n" +
            "group by bp.name")
    List<Vo> getList1();

    @Select("select bt.name,sum(oi.final_total_amount) `value` from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join sku_info si on si.id = od.sku_id\n" +
            "left join base_trademark bt on si.tm_id = bt.id\n" +
            "group by bt.name order by sum(oi.final_total_amount)")
    List<Vo> getList2();

    @Select("select bc1.name,sum(oi.final_total_amount) `value` from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join sku_info si on si.id = od.sku_id\n" +
            "left join base_category3 b on b.id = si.category3_id\n" +
            "left join base_category2 c on c.id = b.category2_id\n" +
            "left join base_category1 bc1 on bc1.id = c.category1_id\n" +
            "group by bc1.name")
    List<Vo> getList3();

    @Select("select '用户人' type, a.oldNum,b.newNum from\n" +
            "(select count(*) oldNum from user_info where timestampdiff(year,create_time,now())>1)a,\n" +
            "(select count(*) newNum from user_info where timestampdiff(year,create_time,now())<=1)b")
    List<Vo1> getList4();

    @Select("select c.name,sum(oi.final_total_amount) `value` from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join sku_info si on si.id = od.sku_id\n" +
            "left join base_category3 b on b.id = si.category3_id\n" +
            "left join base_category2 c on c.id = b.category2_id\n" +
            "group by c.name order by sum(oi.final_total_amount)")
    List<Vo> getList5();

    @Select("select concat(substring(ui.birthday,3,1),'0后') `name`,sum(oi.final_total_amount) `value` from order_info oi\n" +
            "left join user_info ui on oi.user_id = ui.id\n" +
            "group by concat(substring(ui.birthday,3,1),'0后')")
    List<Vo> getList6();

    @Select("select yys`name`,sum(final_total_amount) `value` from order_info where yys is not null group by yys")
    List<Vo> getList7();

    @Select("select\n" +
            "    bt.name,\n" +
            "    if(ui.gender='F','男','女') gender,\n" +
            "    sum(oi.final_total_amount) `value`\n" +
            "from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join sku_info si on si.id = od.sku_id\n" +
            "left join base_trademark bt on si.tm_id = bt.id\n" +
            "left join user_info ui on oi.user_id = ui.id\n" +
            "group by bt.name,ui.gender")
    List<Vo2> getList8();

    @Select("select c.name,sum(oi.final_total_amount) `value` from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join sku_info si on si.id = od.sku_id\n" +
            "left join base_category3 b on b.id = si.category3_id\n" +
            "left join base_category2 c on c.id = b.category2_id\n" +
            "group by c.name")
    List<Vo> getList9();

    @Select("select bc1.name,sum(oi.final_total_amount) `value` from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join sku_info si on si.id = od.sku_id\n" +
            "left join base_category3 b on b.id = si.category3_id\n" +
            "left join base_category2 c on c.id = b.category2_id\n" +
            "left join base_category1 bc1 on bc1.id = c.category1_id\n" +
            "group by bc1.name;")
    List<Vo> getList10();

    @Select("call sp()")
    Double getList11();

    @Select("select s.spu_name `name`,sum(oi.final_total_amount) 'value' from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join sku_info si on od.sku_id = si.id\n" +
            "left join spu_info s on si.spu_id = s.id\n" +
            "group by s.spu_name")
    List<Vo> getList12();
}
